SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE dbo.[DnnProduct_PWSyncDashBoard_LastSyncStatusesDashBoardList]
	@WhereClause nvarchar(1000),
	@SortExpression varchar(50),
	@MaximumRows int = null, 
	@StartRowIndex int = null
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQLString NVARCHAR(1000)
	DECLARE @ParmDefinition NVARCHAR(100)

	/* Build the SQL string once.*/
	SET @SQLString = N'WITH stat AS (	SELECT s.*, ROW_NUMBER() OVER(PARTITION BY s.DBName ORDER BY s.RunTime DESC) AS rk 
										FROM [DnnProduct_PWSyncDashBoard_LastSyncStatuses] s
									), 
						ItemRN AS (		SELECT ROW_NUMBER() OVER(ORDER BY '+ @SortExpression + N') AS RowNum, LastSyncStatusesId, 
												DBName, PWVersion, SyncStatsID, PWAP_AppSchemas, CAPName, PWAP_location, RunTime, Succeeded, 
												FailedStep, FailAt, ErrorDetail, StepDetails,
												ISNULL(DelRec, 0) AS DelRec, ISNULL(ModRec, 0) AS ModRec, ISNULL(NewRec, 0) AS NewRec, [Timestamp] 
										FROM stat WHERE (rk = 1) '

	IF (LEN(@WhereClause) > 0)
	BEGIN
		SET @SQLString = @SQLString + @WhereClause
	END

	SET @SQLString = @SQLString + N' ) SELECT LastSyncStatusesId, DBName, PWVersion, SyncStatsID, CAPName, PWAP_AppSchemas, PWAP_location, RunTime, Succeeded, FailedStep, FailAt, ErrorDetail, StepDetails, [Timestamp] FROM ItemRN WHERE RowNum BETWEEN @startRowIndex+1 AND (@startRowIndex+@maximumRows);'
	SET @ParmDefinition = N'@MaximumRows int, @StartRowIndex int'
	EXECUTE sp_executesql @SQLString, @ParmDefinition, @MaximumRows, @StartRowIndex
	
END
GO
